Ad Widget

**Circle** · 08-08-2022, 14:29

Hello,

Zabbix : 6.0.7
Agent (v1): 6.0.7
template : 0.41 (default one provided with Zabbix 6.0.7)

I am new with this template, I was using before pg_monz to monitor my PostgreSQL DBs but since they stop support for it I turned to give a try with the default official template.

As many of you I have faced the problem with the Status: Ping time but I my case I had 2 problems: first of all it was because the Time was returned with a coma and not a dot and second with the multiplier.

So here is how I fix this:

First dot and comma problem is fixed by changing the first preprocessing step.

step 1: From Time:\s+(\d+\.\d+)\s+ms to Time:\s+(\d+[\.,]\d+)\s+ms

with this both dot and comma are taken into consideration.

Then I need to add a step 2: that will turn the comma into a dot:

So I used the replace feature and has parameter I entered , (comma) and as replacement I put a . (dot).

Now I have the Status: Ping time fully working.

Hope this can help others.

**Circle** · 08-08-2022, 14:52

Hi,

Another solution for people using Solaris/Illumos/SmartOS when I first test the template it was unable to found the psql binary which was of course available on the host, the solution was to add the PATH in the the SMF manifest by doing this:

Code:

sudo svccfg -s svc:/pkgsrc/zabbix:agent setenv PATH /opt/local/bin/:/usr/sbin:/usr/bin:/sbin:/opt/local/bin:/usr/local/sbin:/usr/local/bin

Of cource adapt the PATH value according to your own setup.

Then restart your agent

Code:

sudo svcadm restart zabbix:agent

I hope this can help some of you.

**rezak2887** · 07-10-2022, 15:58

Hi , i have some problem with this template. All work, but i see in logs posgres :
2022-10-07 16:52:13.854 EEST (127.0.0.1) (zbx_monitor@zbx_monitor) (34052) (2/4026) FATAL: database "zbx_monitor" does not exist
But i dont have DB with name zbx_monitor.
If i delete string " UserParameter=pgsql.ping[*], /usr/pgsql-13/bin/pg_isready -U zbx_monitor -h "$1" -p "$2" "
from template_db_postgresql. All ok. But i loss parameter ping.

**mlomach** · 18-10-2022, 16:53

Hello everyone!
Is there any user guide for the PostgreSQL monitoring setup installed on the Windows OS?
The official overview includes:

HTML Code:

"Templates to monitor PostgreSQL by Zabbix. This template was tested on PostgreSQL versions 9.6, 10 and 11 on Linux and Windows"

but I can't find anything about the Zabbix configuration on the Windows side.

Thank you in advance!

**vishu** · 27-04-2023, 13:17

my Hi Team,
template is good to check the database size only but not tables size...if that is also added that will be good.....help is appreciated.my requirement is t monitor the Tables also.

**Iggy** · 01-06-2023, 11:38

Originally posted by mlomach

Hello everyone!
Is there any user guide for the PostgreSQL monitoring setup installed on the Windows OS?
The official overview includes:

HTML Code:

"Templates to monitor PostgreSQL by Zabbix. This template was tested on PostgreSQL versions 9.6, 10 and 11 on Linux and Windows"

but I can't find anything about the Zabbix configuration on the Windows side.

Thank you in advance!

I have the same problem as well.
Unable to setup Windows PostgreSQL monitoring...

Anybody can help?

**otheus** · 02-08-2023, 13:57

Delete post. I missed something in plain sight.

**Circle** · 05-08-2023, 10:07

Hello,

We have faced an issue, due to a misconfiguration the WAL files were not consumed so they start accumulating in the folder.

We didn't detect it immediately, so I have now added a trigger into the template in order to detect it quickly. But this will be lost in case of template update.

Would you mind to add this new trigger in the standard template?

Create a new macro:

{$PG.WAL_NUM_COUNT.MAX.WARN} I set its value to 100.

Then I defined the following trigger:

Name: PostgreSQL: Total number of wal segments is too high

Event name: PostgreSQL: Total number of wal segments is too high (over {$PG.WAL_NUM_COUNT.MAX.WARN} in 5m)

Severity: WARNING

Expression: min(/PostgreSQL by Zabbix agent/pgsql.wal.count,5m)>"${$PG.WAL_NUM_COUNT.MAX.WARN} "

Description: On the server DB {$PG.DB}: select count(*) from pg_ls_waldir();

I guess that it is a useful trigger to implement.

Regards

**aseques** · 11-08-2023, 14:22

Hi, I just found that there's an issue someone reported on the tracker and was closed without accepting the fix, the issue is here, basically there's a super alarming message every minute with the content "FATAL: role "zabbix" does not exist" on the server, also "FATAL: database "zbx_monitor" does not exist", the fix is to instruct the command pg_isready to connect the database with the user zbx_monitor and to check the database postgres (that's present on all systems)
Changing on /etc/zabbix/zabbix_agentd.d/template_db_postgresql.conf

#original
UserParameter=pgsql.ping[*], pg_isready -h "$1" -p "$2"
[HASHTAG="t1101"]fixed[/HASHTAG]
UserParameter=pgsql.ping[*], pg_isready -h "$1" -p "$2" -d "$3" -U "$4"

You have to change also on the server the key for the item PostgreSQL: Ping

#original
pgsql.ping["{$PG.HOST}","{$PG.PORT}"]
[HASHTAG="t1101"]fixed[/HASHTAG]
pgsql.ping["{$PG.HOST}","{$PG.PORT}","{$PG.DATABASE}","{$ PG.U SER}"]

Fixes the issue without any side efects and without any more work

**cheneric** · 31-08-2023, 08:53

Agent 2 - Changed a few things recently at

Browse Zabbix / Zabbix - ZABBIX GIT

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2

**Viewer** · 07-09-2023, 13:49

Hello, Friends,
I want to get metrics with Custom queries.
I placed sql script in folder, change config, but I get error

Code:

relation "mytable" does not exist (SQLSTATE 42P01).

.
I grant only -

Code:

GRANT pg_monitor TO zbx_monitor;

- from here
May be, do I need grant EXECUTE rights?

**zynia** · 14-12-2023, 13:32

Hello.
I see the strange situation after update to in 6.0.25 version.
The number of pgsql.connections highly incresed (Connections sum: Idle)
I have to change Plugins.PostgreSQL.KeepAlive from default falue (300) to Plugins.PostgreSQL.KeepAlive=60.

Code:

postgres=# SELECT row_to_json(T)
FROM (
SELECT
sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active,
sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle,
sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction,
sum(CASE WHEN state = 'idle in transaction (aborted)' THEN 1 ELSE 0 END) AS idle_in_transaction_aborted,
sum(CASE WHEN state = 'fastpath function call' THEN 1 ELSE 0 END) AS fastpath_function_call,
count(*) AS total,
count(*)*100/(SELECT current_setting('max_connections')::int) AS total_pct,
sum(CASE WHEN wait_event IS NOT NULL THEN 1 ELSE 0 END) AS waiting,
(SELECT count(*) FROM pg_prepared_xacts) AS prepared
FROM pg_stat_activity
WHERE datid is not NULL) T;
row_to_json
--------------------------------------------------------------------------------------------------------------------------------------------------------------
{"active":1,"idle":42,"idle_in_transaction":0,"idl e_in_transaction_aborted":0,"fastpath_function_cal l":0,"total":43,"total_pct":4,"waiting":42,"prepar ed":0}[/FONT]

**prasanthi.narra** · 18-12-2023, 23:00

I made use of the PostgreSQL template and was able to get the non psql based checks working. Running into psql: error: /var/lib/zabbix/postgresql/pgsql.wal.stat.sql: Permission denied error for all the psql based checks.

Permissions on /var/lib/zabbix are set to 777 and owned by zabbix user. psql and pg_isready are in the paths.

I'm able to run this query as zabbix user from the host using the below command
su - zabbix -s /bin/bash -c 'psql -qtAX postgresql://"zbx_monitor":"xxxxxxx"@"10.194.231.89":"5432"/"postgres" -f "/var/lib/zabl.stat.sql"'ql/pgsql.wal

The same doesn't seem to run from the zabbix agent/UI. I'm not allowed to make SELinux permissible per our company policies. Moving the .sql files to my home directory and running it from there did not resolve the permissions issue either. Any other recommendations or suggestions to get this working.

**sandro1976** · 21-03-2024, 17:09

Hello everyone.
I'd like to have an hint from you about something happened today.

I monitor a few debian 10 machines with pgsql 11 on board through the zabbix-agent2. Every pgsql server has several users and db.
zabbix-agent2: 1:6.0.27-1+debian11
The zabbix server is a 1:6.0.27-1+debian11 on a debian 11.

Today I got a "overload" of connections on one machine (they are all configured with default max_connections = 100) and no trigger was fired.
I fixed the problem and then I investigated:

psql (11.22 (Debian 11.22-0+deb10u2))
Type "help" for help.

postgres=# select count(*) from pg_stat_activity;
count
-------
70
(1 row)

postgres=# SHOW MAX_CONNECTIONS;
max_connections
-----------------
100
(1 row)

So I would expect the current value of pgsql.connections.total_pct item to be at 70% but the actual value has been at 16% for all day (see https://pasteboard.co/TPN2WLrVJfSd.png )

What's wrong?
How can I proceed to debug this?
Maybe not all connections are counted by default? Do I need to set any macro?

Thanks a lot.

**sandro1976** · 26-03-2024, 18:41

Originally posted by sandro1976

Hello everyone.
I'd like to have an hint from you about something happened today.

I monitor a few debian 10 machines with pgsql 11 on board through the zabbix-agent2. Every pgsql server has several users and db.
zabbix-agent2: 1:6.0.27-1+debian11
The zabbix server is a 1:6.0.27-1+debian11 on a debian 11.

Today I got a "overload" of connections on one machine (they are all configured with default max_connections = 100) and no trigger was fired.
I fixed the problem and then I investigated:

psql (11.22 (Debian 11.22-0+deb10u2))
Type "help" for help.

postgres=# select count(*) from pg_stat_activity;
count
-------
70
(1 row)

postgres=# SHOW MAX_CONNECTIONS;
max_connections
-----------------
100
(1 row)

So I would expect the current value of pgsql.connections.total_pct item to be at 70% but the actual value has been at 16% for all day (see https://pasteboard.co/TPN2WLrVJfSd.png )

What's wrong?
How can I proceed to debug this?
Maybe not all connections are counted by default? Do I need to set any macro?

Thanks a lot.

Ok, I am going to answer to myself :-)
I fixed the counting "granting" pg_monitor to the zabbix user on the postgres dbms.

Ad Widget

Discussion thread for official Zabbix Template DB PostgreSQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment